const schema = process.env.MYSQL_DATABASE

// 标记冲突字段，和筛选请求名称保持一致
export const getTableCols = (colConfig) => {
  const allCols = []
  const conflictCols = []
  colConfig.filter(e => !e.is_subQuery).forEach(e => {
    if (isNilOrEmpty(e.table_cols_selected)) return
    e.table_cols_selected.forEach(colName => {
      const idx = allCols.findIndex(e => e === colName)
      if (idx > -1) {
        conflictCols.push(colName)
      }
      allCols.push(colName)
    })
  })
  const cols = {}
  colConfig.forEach(e => {
    if (isNilOrEmpty(e.table_cols_selected)) return
    cols[e.name] = e.table_cols_selected.map(colName => {
      if (isNotNilOrEmpty(conflictCols.includes(colName))) {
        return `${e.name}__${colName}`
      }
      return colName
    })
  })
  return cols
}

const joinOptions = [
  { key: 'left_join', operator: '<', text: 'LEFT JOIN' },
  { key: 'right_join', operator: '>', text: 'RIGHT JOIN' },
  { key: 'inner_join', operator: '&', text: 'INNER JOIN' },
  { key: 'full_join', operator: '|', text: 'FULL JOIN' },
  { key: 'outer_join', operator: '!', text: 'OUTER JOIN' },
  { key: 'cross_join', operator: '*', text: 'CROSS JOIN' },
  { key: 'side_join', operator: '^', text: 'SIDE JOIN' },
  { key: 'anti_join', operator: '(', text: 'ANTI JOIN' },
  { key: 'foreign_join', operator: ')', text: 'FOREIGN JOIN' },
]

const isNumber = (val) => {
  return NUMERIC_DATA_TYPES.includes(val.replace(' unsigned', ''))
}
const isDate = (val) => {
  return DATE_TIME_DATA_TYPES.includes(val)
}
// 字段内容格式化：
// 1. 数值类型，不加引号
// 2. 字符、日期要加引号
// 3. 带逗号的字符串，日期型转 BETWEEN，其他类型转 IN
// 4. IN 需要加括号
const sqlValFormat = (typStr, val) => {
  if (isNumber(typStr)) { return val }
  if (val.includes(',')) {
    return val.split(',').map(e => `'${e}'`).join(',')
  }
  return `'${val}'`
}
export const genSql = ({ reqJson, colConfig, tableCols, userId, limit, offset }) => {
  let sqlStr = ''
  let sqlCountStr = ''
  const mainField = []
  const mainWhere = []
  const mainOrder = []
  let hasGroupBy = false
  let subQueryClean = ''
  colConfig.forEach((e, idx) => {
    const subWhere = []
    // 接口客户端传参
    Object.keys(reqJson).filter(v => v !== '@order').forEach(v => {
      // 根据表格配置过滤传参
      if (!tableCols[e.name].includes(v)) { return }
      // 查找字段类型
      const fieldType = e.table_cols.find(n => n.fieldName === v)?.fieldType
      // 获取冲突字段的参数名称的正常字段名
      const fieldNameOrig = v.includes(`${e.name}__`) ? v.replace(`${e.name}__`, '') : v
      const fieldName = `${e.name}.${fieldNameOrig}`
      const fieldVal = reqJson[v]
      if (isArray(fieldVal)) {
        // 数组 -> between, 日期或数值使用
        if (fieldVal[0].includes(',')) {
          // 多段范围   |  "age": ["18, 20", "30, 35"]
          fieldVal.forEach(n => {
            const btwVal = fieldVal[n].split(',')
            subWhere.push(`${fieldName} BETWEEN ${sqlValFormat(fieldType, btwVal[0])} AND ${sqlValFormat(fieldType, btwVal[1])} `)
          })
        } else {
          // 范围  | "age": [18, 20]
          subWhere.push(`${fieldName} BETWEEN ${sqlValFormat(fieldType, fieldVal[0])} AND ${sqlValFormat(fieldType, fieldVal[1])} `)
        }
      } else if (type(fieldVal) === 'Number' || type(fieldVal) === 'Boolean') {
        // 数值
        subWhere.push(`${fieldName} = ${fieldVal}`)
      } else if (/^\//.test(fieldVal) && /\/$/.test(fieldVal)) {
        // 正则 | "name": "/^[0-9]+$/"
        subWhere.push(`${fieldName} REGEXP '${fieldVal.replace(/\s/g, '').slice(1, -1)}'`)
      } else if (/^set=/.test(fieldVal)) {
        // FIND_IN_SET  | "name": "set=1,3"
        const list = fieldVal.replace('set=', '').split(',')
        const setList = []
        list.forEach(n => {
          setList.push(`FIND_IN_SET('${n}', ${fieldName}) > 0`)
        })
        subWhere.push(`(${setList.join(' OR ')})`)
      } else if (fieldVal.includes(',')) {
        // 带逗号的字符串，如果是日期型，转成 between；如果是其他类型，转成 in
        if (isDate(fieldType)) {
          const btwVal = fieldVal.split(',')
          subWhere.push(`${fieldName} BETWEEN ${sqlValFormat(fieldType, btwVal[0])} AND ${sqlValFormat(fieldType, btwVal[1])} `)
        } else {
          // IN  | "name": "rose, tom"
          subWhere.push(`${fieldName} IN (${sqlValFormat(fieldType, fieldVal)})`)
        }
      } else if (fieldVal.replace(/\s/g, '') === 'NULL') {
        // 空  | "name": "NULL"
        subWhere.push(`${fieldName} IS NULL`)
      } else if (fieldVal.replace(/\s/g, '') === '!NULL') {
        // 非空 | "name": "! NULL"
        subWhere.push(`${fieldName} IS NOT NULL`)
      } else if (fieldVal.includes('%')) {
        // 模糊查询
        subWhere.push(`${fieldName} LIKE '${fieldVal}'`)
      } else if (/[=><!]+/.test(fieldVal)) {
        // >, >=, <, <=, !
        const val = fieldVal.match(/[^=><!]+/)[0]
        const sig = fieldVal.replace(val, '')
        subWhere.push(`${fieldName} ${sig} ${val}`)
      } else {
        subWhere.push(`${fieldName} = '${fieldVal.replace(/\s/g, '')}'`)
      }
    })
    //  将 join 筛选条件加入子查询
    if (isNotNilOrEmpty(subWhere)) {
      subWhere.forEach(v => {
        mainWhere.push(v)
      })
    }
    const tableOrder = []
    const updateOrder = (val) => {
      const orders = val.split(',')
      orders.forEach(v => {
        const orderField = v.replace(/\s/g, '').replace('+', '').replace('-', '')
        if (tableCols[e.name].includes(orderField)) {
          const fieldNameOrig = orderField.replace(`${e.name}__`, '')
          let sortType = ''
          if (v.includes('+')) { sortType = 'asc' }
          if (v.includes('-')) { sortType = 'desc' }
          const tIdx = tableOrder.findIndex(n => n.fieldName === `${e.name}.${fieldNameOrig}`)
          if (tIdx < 1) {
            tableOrder.push({ fieldName: `${e.name}.${fieldNameOrig}`, sortType })
          }
          const mIdx = mainOrder.findIndex(n => n.fieldName === `${e.name}.${fieldNameOrig}`)
          if (mIdx < 1) {
            mainOrder.push({ fieldName: `${e.name}.${fieldNameOrig}`, sortType })
          }
        }
      })
    }
    // 从接口请求中筛选出本表的排序字段
    if (isNotNilOrEmpty(reqJson['@order'])) {
      updateOrder(reqJson['@order'])
    }
    // 后台配置的字段排序
    if (isNotNilOrEmpty(e.table_cols_order)) {
      const orderStr = e.table_cols_order.map(v => `${v.fieldName}${v.orderType}`).join(',')
      updateOrder(orderStr)
    }
    // 后台配置的接口参数
    let subWhereStr = ''
    if (isNotNilOrEmpty(e.table_cols_default_filter)) {
      e.table_cols_default_filter.forEach((v, fIdx) => {
        const fieldType = e.table_cols.find(n => n.fieldName === v.fieldName)?.fieldType
        if (fIdx !== 0) {
          subWhereStr += ' AND '
        }
        if (['NULL', 'NOT NULL'].includes(v.filterOperator)) {
          subWhereStr += `${e.name}.${v.fieldName} IS ${v.filterOperator}`
        } else if (['OR', 'AND'].includes(v.filterOperator)) {
          const fieldval = v.filterValue.split(',')
          subwherestr += `${e.name}.${v.fieldName} ${sqlValFormat(fieldType, fieldval[0])} ${v.filterOperator} ${e.name}.${v.fieldName} ${sqlValFormat(fieldType, fieldval[1])} }`
        } else if (['IN', 'NOT IN'].includes(v.filterOperator)) {
          subWhereStr += `${e.name}.${v.fieldName} ${v.filterOperator} (${sqlValFormat(fieldType, v.filterValue)})`
        } else if (v.filterOperator === 'BETWEEN') {
          const fieldVal = v.filterValue.split(',')
          if (fieldVal[0].includes(',')) {
            // 多段范围   |   "18, 20", "30, 35"
            fieldVal.forEach(n => {
              const btwVal = fieldVal[n].split(',')
              subWhereStr += `${e.name}.${v.fieldName} BETWEEN ${sqlValFormat(fieldType, btwVal[0])} AND ${sqlValFormat(fieldType, btwVal[1])}`
            })
          } else {
            // 普通范围 | 2017-10-01,2018-10-01
            const btwVal = fieldVal.split(',')
            subWhereStr += `${e.name}.${v.fieldName} BETWEEN ${sqlValFormat(fieldType, btwVal[0])} AND ${sqlValFormat(fieldType, btwVal[1])}`
          }
        } else {
          subWhereStr += `${e.name}.${v.fieldName} ${v.filterOperator} ${sqlValFormat(fieldType, v.filterValue)}`
        }
      })
      subWhere.push(subWhereStr)
      mainWhere.push(subWhereStr)
    }
    // 后台配置的用户id条件
    if (isNotNilOrEmpty(e.table_cols_header_userid) && isNotNilOrEmpty(userId)) {
      e.table_cols_header_userid.forEach(v => {
        subWhereStr = `${e.name}.${v} = ${userId}`
        subWhere.push(subWhereStr)
        mainWhere.push(subWhereStr)
      })
    }
    if (idx === 0) {
      // 主表，将字段保存起来，后面处理。id要单独处理
      tableCols[e.name].forEach(v => {
        if (v.includes(`${e.name}__`)) {
          const fieldOrg = v.replace(`${e.name}__`, '')
          mainField.push(`${e.name}.${fieldOrg} AS ${v}`)
          if (fieldOrg === 'id') {
            mainField.push(`${e.name}.id AS id`)
          }
          return
        }
        mainField.push(`${e.name}.${v}`)
      })
    } else {
      const sqlFields = tableCols[e.name].map(n => {
        if (n.includes(`${e.name}__`)) {
          const fieldOrg = n.replace(`${e.name}__`, '')
          return `${fieldOrg} AS ${n}`
        }
        return n
      })
      const subWhereStrJoin = isNilOrEmpty(subWhere) ? '' : `WHERE ${subWhere.join(' AND ')}`
      const subOrder = isNilOrEmpty(tableOrder) ? '' : `ORDER BY ${tableOrder.map(n => `${n.fieldName} ${n.sortType}`).join(',')}`
      e.table_cols_join.forEach(v => {
        const joinOption = joinOptions.find(n => n.operator === v.joinOperator)
        if (isNilOrEmpty(joinOption)) {
          return
        }
        const joinText = joinOption.text
        const fieldAlias = tableCols[e.name].includes(`${e.name}__${v.fieldName}`) ? `${e.name}__${v.fieldName}` : v.fieldName
        const subSqlStrSuf = `ON ${e.name}.${fieldAlias} = ${v.joinTable}.${v.joinTableField}`
        const subSqlStr = `${joinText} (SELECT ${sqlFields.join(',')} FROM ${schema}.${e.name} ${subWhereStrJoin} ${subOrder}) AS ${e.name} ${subSqlStrSuf}`
        sqlStr += ` ${subSqlStr} `
        if (!e.is_subQuery) {
          sqlCountStr += ` ${subSqlStr} `
        }
      })
      if (isNotNilOrEmpty(sqlStr)) {
        if (!e.is_subQuery) {
          mainField.push(`${e.name}.*`)
        } else {
          const jsonArr = sqlFields.map(v => `'${v}',${e.name}.${v}`).join(',')
          mainField.push(`JSON_ARRAYAGG(JSON_OBJECT(${jsonArr})) AS ${e.name}_dataList`)
          hasGroupBy = true
          subQueryClean = `${e.name}_dataList.${e.table_cols_join[0].fieldName}`
        }
      }
    }
  })
  const mainWhereSql = isNilOrEmpty(mainWhere) ? '' : `WHERE ${mainWhere.join(' AND ')}`
  const mainSql = `SELECT ${mainField.join(',')} FROM ${colConfig[0].name} `
  const mainOrderStr = isNilOrEmpty(mainOrder) ? '' : `ORDER BY ${mainOrder.map(n => `${n.fieldName} ${n.sortType}`).join(',')}`
  const sql = `${mainSql} ${sqlStr} ${mainWhereSql} ${hasGroupBy ? `GROUP BY ${colConfig[0].name}.id` : ''} ${mainOrderStr} limit ${limit} offset ${offset}`
  const countSql = `SELECT count(${colConfig[0].name}.id) AS count FROM ${colConfig[0].name} ${sqlCountStr} ${mainWhereSql}`
  return { sql, countSql, subQueryClean }
}
